<?php

include_once './Utils.php';
include_once './MySQLConnect.php';
include_once 'PHPUnit/Framework.php';

// the db connection config file for testing purposes
define('TEST_DB_CONFIG_FILE', './test_db_config.php');

if(file_exists(TEST_DB_CONFIG_FILE)) {
    echo "\nTest config file found at '" . TEST_DB_CONFIG_FILE . "'!\n";
    
    include_once TEST_DB_CONFIG_FILE;
}
else {
    echo "\nTest config file not found, the default config will be used!\n";

    // default connection configuration
    define('MYSQL_HOST', '127.0.0.1'); // localhost doesn't work, use 127.0.0.1 !!!
    define('MYSQL_USER', 'root');
    define('MYSQL_PASS', '');
    define('MYSQL_DB', 'situare_test');
}


/**
 * PHPUnit test for class MySQLConnect.
 *
 * @author lacci
 */
class MySQLConnectTest extends PHPUnit_Framework_TestCase {   

    public static function setUpBeforeClass() {
        parent::setUpBeforeClass();
        
        try {
            MySQLConnect::setConnectionConfig(MYSQL_HOST, MYSQL_USER, MYSQL_PASS, MYSQL_DB);
        }
        catch (Exception $e) {
            echo $e->getTraceAsString();
            self::fail("Exception: " . $e->getCode() . "] " . $e->getMessage());
        }
        
        // clear the 'user' table
        self::clearTable('user');
        self::assertEquals(0, count(MySQLConnect::executeSelect("SELECT * FROM `user`;")));

        // clear the 'message' table
        self::clearTable('message');
        self::assertEquals(0, count(MySQLConnect::executeSelect("SELECT * FROM `message`;")));
        
        // clear the 'tag' table
        self::clearTable('tag');
        self::assertEquals(0, count(MySQLConnect::executeSelect("SELECT * FROM `tag`;")));
        
        // clear the 'usertag' table
        self::clearTable('usertag');
        self::assertEquals(0, count(MySQLConnect::executeSelect("SELECT * FROM `usertag`;")));
        
        MySQLConnect::executeInsertOrUpdateOrDelete("INSERT INTO user(uid) VALUES (123), (456), (789), (111), (222);");
    }

    protected static function clearTable($tableName) {
        if (!$mysqli = mysqli_connect(MYSQL_HOST, MYSQL_USER, MYSQL_PASS, MYSQL_DB)) {
            self::fail("Can't connect to DB to clear the table '$tableName'");
        }

        $result = mysqli_query($mysqli, "TRUNCATE TABLE `$tableName`;");
		
        self::assertTrue($result, "Can't clear the table '$tableName'! DBError: [" . mysqli_errno($mysqli) . "] " . mysqli_error($mysqli));

        mysqli_close($mysqli);
    }
    
    protected static function initInterestingPeopleTables() {
        self::clearTable('tag');
        self::assertEquals(0, count(MySQLConnect::executeSelect("SELECT * FROM `tag`;")));
        self::clearTable('usertag');
        self::assertEquals(0, count(MySQLConnect::executeSelect("SELECT * FROM `usertag`;")));
        self::clearTable('friend');
        self::assertEquals(0, count(MySQLConnect::executeSelect("SELECT * FROM `friend`;")));
        self::clearTable('location');
        self::assertEquals(0, count(MySQLConnect::executeSelect("SELECT * FROM `location`;")));
        
        MySQLConnect::insertTag(123, "rock");
        MySQLConnect::insertTag(123, "pop");
        MySQLConnect::insertTag(456, "rock");
        MySQLConnect::insertTag(789, "britney spears");
        MySQLConnect::insertTag(789, "pop");
        MySQLConnect::insertTag(111, "pop");
        MySQLConnect::insertTag(111, "britney spears");
        MySQLConnect::insertTag(222, "pop");
        MySQLConnect::insertTag(222, "popcorn");
        self::assertEquals(9, count(MySQLConnect::executeSelect("SELECT * FROM `usertag`;")));
        
        MySQLConnect::insertFriends(123, array(111, 456));
        self::assertEquals(4, count(MySQLConnect::executeSelect("SELECT * FROM `friend`;")));
        
        MySQLConnect::executeInsertOrUpdateOrDelete("INSERT INTO location(g, timestamp, uid) VALUES
            (GeomFromText('Point(20 34)'), NOW(), 111), (GeomFromText('Point(65 25)'), NOW(), 456), 
            (GeomFromText('Point(65.01 25)'), NOW(), 123), (GeomFromText('Point(65.01 25.002)'), NOW(), 789),
            (GeomFromText('Point(69.01 23.002)'), NOW(), 222)");
        self::assertEquals(5, count(MySQLConnect::executeSelect("SELECT * FROM `location`;")));
    }

    public function testInsertMessage() {
        $insertedMessagesCount1 = MySQLConnect::insertMessage(123, 789, "First message", 25.5, 66, "Address");
        self::assertEquals(1, $insertedMessagesCount1, "Wrong number of inserted messages!");
        self::assertEquals(1, count(MySQLConnect::executeSelect("SELECT * FROM `message`;")));
        
        $insertedMessagesCount2 = MySQLConnect::insertMessage(123, 789, "Second message", 25.5, 66, "Address");
        self::assertEquals(1, $insertedMessagesCount2, "Wrong number of inserted messages!");
        self::assertEquals(2, count(MySQLConnect::executeSelect("SELECT * FROM `message`;")));
        
        $result1 = MySQLConnect::executeSelect("SELECT sid, rid, Y(g) as lat, X(g) as lon FROM `message` WHERE `sid`=123;");
		self::assertEquals(2, count($result1), "Wrong count of found records!");
		self::assertEquals(123, $result1[0]['sid'], "Wrong sender ID!");
		self::assertEquals(789, $result1[0]['rid'], "Wrong receiver ID!");
		self::assertEquals(25.5, $result1[0]['lat'], "Wrong latitude!");
		self::assertEquals(66, $result1[0]['lon'], "Wrong longitude!");
		self::assertEquals(123, $result1[1]['sid'], "Wrong sender ID!");
		self::assertEquals(789, $result1[1]['rid'], "Wrong receiver ID!");
		self::assertEquals(25.5, $result1[1]['lat'], "Wrong latitude!");
		self::assertEquals(66, $result1[1]['lon'], "Wrong longitude!");
		
		$insertedMessagesCount3 = MySQLConnect::insertMessage(456, 789, "Third message", NULL, NULL, NULL);
        self::assertEquals(1, $insertedMessagesCount3, "Wrong number of inserted messages!");
        self::assertEquals(3, count(MySQLConnect::executeSelect("SELECT * FROM `message`;")));
        
        $insertedMessagesCount4 = MySQLConnect::insertMessage(789, 456, "Fourth message", NULL, NULL, NULL);
        self::assertEquals(1, $insertedMessagesCount4, "Wrong number of inserted messages!");
        self::assertEquals(4, count(MySQLConnect::executeSelect("SELECT * FROM `message`;")));
        
        $result2 = MySQLConnect::executeSelect("SELECT sid, rid, Y(g) as lat, X(g) as lon FROM `message` WHERE `sid`=456;");
        self::assertEquals(1, count($result2), "Wrong count of found records!");
        self::assertEquals(NULL, $result2[0]['lat'], "Wrong latitude!");
        self::assertEquals(NULL, $result2[0]['lon'], "Wrong longitude!");
    }
    
    public function testGetReceivedMessages() {
		self::assertEquals(4, count(MySQLConnect::executeSelect("SELECT * FROM `message`;")));
		$receivedMessages = MySQLConnect::getReceivedMessages(789);
		self::assertEquals(3, count($receivedMessages), "Wrong count of found records!");
        $receivedMessages = MySQLConnect::getReceivedMessages(123);
		self::assertEquals(0, count($receivedMessages), "Wrong count of found records!");
    }
    
    public function testGetSentMessages() {
		self::assertEquals(4, count(MySQLConnect::executeSelect("SELECT * FROM `message`;")));
		$receivedMessages = MySQLConnect::getSentMessages(789);
		self::assertEquals(1, count($receivedMessages), "Wrong count of found records!");
    }
    
    public function testRemoveMessage() {
		self::assertEquals(4, count(MySQLConnect::executeSelect("SELECT * FROM `message`;")));
		MySQLConnect::removeMessage(1, 789);
		self::assertEquals(3, count(MySQLConnect::executeSelect("SELECT * FROM `message`;")));
		$result1 = MySQLConnect::executeSelect("SELECT mid, sid, rid FROM `message`;");
		self::assertEquals(2, $result1[0]['mid'], "Wrong message ID!");
		self::assertEquals(3, $result1[1]['mid'], "Wrong message ID!");
        self::assertEquals(4, $result1[2]['mid'], "Wrong message ID!");
		
		MySQLConnect::removeMessage(2, 123456);
		self::assertEquals(3, count(MySQLConnect::executeSelect("SELECT * FROM `message`;")));
		
		MySQLConnect::removeMessage(2, 123);
		self::assertEquals(2, count(MySQLConnect::executeSelect("SELECT * FROM `message`;")));
		$result2 = MySQLConnect::executeSelect("SELECT mid, sid, rid FROM `message`;");
		self::assertEquals(3, $result2[0]['mid'], "Wrong message ID!");
	}
    
    public function testInsertOrUpdateTag() {
        $tagId = MySQLConnect::insertOrUpdateTag("pop");
        self::assertEquals(1, count(MySQLConnect::executeSelect("SELECT * FROM `tag`;")));
        $result1 = MySQLConnect::executeSelect("SELECT tid, name, count FROM `tag`;");
        self::assertEquals("pop", $result1[0]['name'], "Wrong tag name!");
        self::assertEquals(1, $result1[0]['count'], "Wrong tag count!");
        
        $tagId2 = MySQLConnect::insertOrUpdateTag("pop");
        self::assertTrue($tagId == $tagId2);
        $result2 = MySQLConnect::executeSelect("SELECT tid, name, count FROM `tag`;");
        self::assertEquals(2, $result2[0]['count'], "Wrong tag count!");
    }
    
    public function testInsertUserTag() {
        $count1 = MySQLConnect::insertUserTag(789, 1);
        self::assertEquals(1, $count1, "Wrong tag count!");
        
        try {
            MySQLConnect::insertUserTag(789, 1);
        }
        catch (Exception $e) {
            self::assertEquals(1062, $e->getCode(), "Wrong DB error code!");
        }
    }
    
    public function testInsertTag() {
        MySQLConnect::insertTag(789, "rock");
        $tagResult1 = MySQLConnect::executeSelect("SELECT tid, name, count FROM `tag` WHERE name = 'rock';");
        self::assertEquals(1, $tagResult1[0]['count'], "Wrong tag count");
        
        $tagId = $tagResult1[0]['tid'];
        $userTagResult1 = MySQLConnect::executeSelect("SELECT uid, tid FROM usertag WHERE tid = $tagId;");
        self::assertEquals(1, count($userTagResult1));
        self::assertEquals(789, $userTagResult1[0]['uid'], "Wrong user ID!");
        
        try {
            MySQLConnect::insertTag(789, "rock");
        }
        catch (Exception $e) {
            self::assertEquals(1062, $e->getCode(), "Wrong DB error code!");
            $tagResult1 = MySQLConnect::executeSelect("SELECT tid, name, count FROM `tag` WHERE name = 'rock';");
            self::assertEquals(2, $tagResult1[0]['count'], "Wrong tag count");
        }
    }
    
    public function testGetUserTags() {
        $userTags = MySQLConnect::getUserTags(789);
        self::assertEquals(2, count($userTags));
        self::assertEquals("pop", $userTags[0]['name'], "Wrong tag name!");
        self::assertEquals("rock", $userTags[1]['name'], "Wrong tag name!");
    }
    
    public function testRemoveTag() {
        $affectedRows = MySQLConnect::removeTag(789, 1);
        self::assertEquals(1, $affectedRows);
        
        $userTagResult1 = MySQLConnect::executeSelect("SELECT uid, tid FROM usertag");
        self::assertEquals(1, count($userTagResult1));
        
        $affectedRows = MySQLConnect::removeTag(123, 3);
        self::assertEquals(0, $affectedRows);
    }
    
    public function testGetPopularTags() {
        MySQLConnect::insertTag(123, "britney spears");
        MySQLConnect::insertTag(234, "britney spears");
        MySQLConnect::insertTag(345, "britney spears");
        
        $popularTags = MySQLConnect::getPopularTags();
        self::assertEquals(3, count($popularTags));
        self::assertEquals("britney spears", $popularTags[0]['name'], "Wrong tag name!");
        self::assertEquals("pop", $popularTags[1]['name'], "Wrong tag name!");
        self::assertEquals("rock", $popularTags[2]['name'], "Wrong tag name!");
        
    }
    
    public function testGetFriendsFromArea() {
        self::initInterestingPeopleTables();
        
        $friendsFromTheArea1 = MySQLConnect::getFriendsFromArea(123, 2);
        self::assertEquals(1, count($friendsFromTheArea1));
        self::assertEquals(456, $friendsFromTheArea1[0]['uid'], "Wrong user ID!");
        
        $friendsFromTheArea2 = MySQLConnect::getFriendsFromArea(123, 0.02);
        self::assertEquals(0, count($friendsFromTheArea2));
        
        $friendsFromTheArea3 = MySQLConnect::getFriendsFromArea(333, 1000);
        self::assertEquals(0, count($friendsFromTheArea3));
    }
    
    public function testGetOthersFromArea() {
        self::initInterestingPeopleTables();
        
        $othersFromTheArea1 = MySQLConnect::getOthersFromArea(123, 2);
        self::assertEquals(1, count($othersFromTheArea1));
        self::assertEquals(789, $othersFromTheArea1[0]['uid'], "Wrong user ID!");
        
        $othersFromTheArea1 = MySQLConnect::getOthersFromArea(123, 2000);
        self::assertEquals(2, count($othersFromTheArea1));
        self::assertEquals(789, $othersFromTheArea1[0]['uid'], "Wrong user ID!");
        self::assertEquals(222, $othersFromTheArea1[1]['uid'], "Wrong user ID!");
    }
    
    public function testGetFriendsWithTag() {
		self::initInterestingPeopleTables();
		
		$friendsWithTag1 = MySQLConnect::getFriendsWithTag(123, 50, "rock");
		self::assertEquals(1, count($friendsWithTag1));
		self::assertEquals(456, $friendsWithTag1[0]['uid'], "Wrong user ID!");
		
		$friendsWithTag2 = MySQLConnect::getFriendsWithTag(123, 50, "roc");
		self::assertEquals(1, count($friendsWithTag2));
		self::assertEquals(456, $friendsWithTag2[0]['uid'], "Wrong user ID!");
		
		$friendsWithTag3 = MySQLConnect::getFriendsWithTag(123, 5000, "britney spears");
		self::assertEquals(1, count($friendsWithTag3));
		self::assertEquals(111, $friendsWithTag3[0]['uid'], "Wrong user ID!");
		
		$friendsWithTag4 = MySQLConnect::getFriendsWithTag(123, 50, "metal");
		self::assertEquals(0, count($friendsWithTag4));
	}
	
	public function testGetOthersWithTag() {
		self::initInterestingPeopleTables();
		
		$othersWithTag1 = MySQLConnect::getOthersWithTag(123, 50, "britney spears");
		self::assertEquals(1, count($othersWithTag1));
		self::assertEquals(789, $othersWithTag1[0]['uid'], "Wrong user ID!");
		
		$othersWithTag2 = MySQLConnect::getOthersWithTag(123, 500, "po");
		self::assertEquals(2, count($othersWithTag2));
		self::assertTrue(($othersWithTag2[0]['uid'] == 222) || ($othersWithTag2[0]['uid'] == 789), "Wrong user ID!");
		self::assertTrue(($othersWithTag2[1]['uid'] == 222) || ($othersWithTag2[1]['uid'] == 789), "Wrong user ID!");
		
		$othersWithTag3 = MySQLConnect::getOthersWithTag(123, 50, "rock");
		self::assertEquals(0, count($othersWithTag3));
	}
}
?>

